international Debt Statistics Analysis

import pandas as pd
import sqlite3
import sql
from itables import init_notebook_mode

In this notebook, I am analyzing international debt data collected by The World Bank. The dataset contains information about the amount of debt (in USD) owed by developing countries across several categories. I am going to find the answers to questions like:

• What is the total amount of debt that is owed by the countries listed in the dataset?
• Which country owns the maximum amount of debt and what does that amount look like?
• What is the average amount of debt owed by countries across different debt indicators?

Below is a snapshot of the database I will be working with:

country_name	country_code	indicator_name	indicator_code	debt
Afghanistan	AFG	“Disbursements on external debt, long-term (DIS, current US\()" |DT.DIS.DLXF.CD|72894453.7 | |Afghanistan |AFG |"Interest payments on external debt, long-term (INT, current US\))”	DT.INT.DLXF.CD	53239440.1
Afghanistan	AFG	“PPG, bilateral (AMT, current US\()" |DT.AMT.BLAT.CD|61739336.9 | |Afghanistan |AFG |"PPG, bilateral (DIS, current US\))”	DT.DIS.BLAT.CD	49114729.4
Afghanistan	AFG	“PPG, bilateral (INT, current US\()" |DT.INT.BLAT.CD|39903620.1 | |Afghanistan |AFG |"PPG, multilateral (AMT, current US\))”	DT.AMT.MLAT.CD	39107845
Afghanistan	AFG	“PPG, multilateral (DIS, current US\()" |DT.DIS.MLAT.CD|23779724.3 | |Afghanistan |AFG |"PPG, multilateral (INT, current US\))”	DT.INT.MLAT.CD	13335820
Afghanistan	AFG	“PPG, official creditors (AMT, current US\()" |DT.AMT.OFFT.CD|100847181.9| |Afghanistan |AFG |"PPG, official creditors (DIS, current US\))”	DT.DIS.OFFT.CD	72894453.7

Data Overview

init_notebook_mode(all_interactive=True)

df = pd.read_csv("international_debt.csv") 

conn = sqlite3.connect(":memory:")

df.to_sql("inter_debt", conn, index=False, if_exists="replace") 

2357

# Load the SQL extension
%load_ext sql

# Register the existing in-memory SQLite connection in jupysql

%sql conn --alias inter_debttable

%%sql
SELECT *
FROM inter_debt

Running query in 'inter_debttable'

country_name	country_code	indicator_name	indicator_code	debt
Afghanistan	AFG	"Disbursements on external debt, long-term (DIS, current US$)"	DT.DIS.DLXF.CD	72894453.7
Afghanistan	AFG	"Interest payments on external debt, long-term (INT, current US$)"	DT.INT.DLXF.CD	53239440.1
Afghanistan	AFG	"PPG, bilateral (AMT, current US$)"	DT.AMT.BLAT.CD	61739336.9
Afghanistan	AFG	"PPG, bilateral (DIS, current US$)"	DT.DIS.BLAT.CD	49114729.4
Afghanistan	AFG	"PPG, bilateral (INT, current US$)"	DT.INT.BLAT.CD	39903620.1
Afghanistan	AFG	"PPG, multilateral (AMT, current US$)"	DT.AMT.MLAT.CD	39107845.0
Afghanistan	AFG	"PPG, multilateral (DIS, current US$)"	DT.DIS.MLAT.CD	23779724.3
Afghanistan	AFG	"PPG, multilateral (INT, current US$)"	DT.INT.MLAT.CD	13335820.0
Afghanistan	AFG	"PPG, official creditors (AMT, current US$)"	DT.AMT.OFFT.CD	100847181.9
Afghanistan	AFG	"PPG, official creditors (DIS, current US$)"	DT.DIS.OFFT.CD	72894453.7

Truncated to displaylimit of 10.

What is the total amount of debt that is owed by the countries listed in the dataset?

%%sql
SELECT COUNT(DISTINCT country_name) AS total_distinct_countries 
FROM inter_debt

Running query in 'inter_debttable'

total_distinct_countries
124

%%sql
SELECT DISTINCT indicator_name AS distinct_debt_indicators 
FROM inter_debt

Running query in 'inter_debttable'

distinct_debt_indicators
"Disbursements on external debt, long-term (DIS, current US$)"
"Interest payments on external debt, long-term (INT, current US$)"
"PPG, bilateral (AMT, current US$)"
"PPG, bilateral (DIS, current US$)"
"PPG, bilateral (INT, current US$)"
"PPG, multilateral (AMT, current US$)"
"PPG, multilateral (DIS, current US$)"
"PPG, multilateral (INT, current US$)"
"PPG, official creditors (AMT, current US$)"
"PPG, official creditors (DIS, current US$)"

Truncated to displaylimit of 10.

Meaning of every indicators:

Disbursements on External Debt, Long-Term (DIS, current US\()**: measures the flow of newly issued long-term debt in a given time period.<br> **Interest Payments on External Debt, Long-Term (INT, current US\)): represents the cost of borrowing (interest paid) on long-term external debt.
PPG, Bilateral (AMT, current US\()**: The total amount of government or government-guaranteed debt owed to foreign governments.<br> **PPG, Bilateral (DIS, current US\)): The total amount of new bilateral loans disbursed.
PPG, Bilateral (INT, current US\()**: The cost of interest payments on government or government-guaranteed bilateral loans.<br> **PPG, Multilateral (AMT, current US\)): The total outstanding amount of government or government-guaranteed loans from international organizations.
PPG, Multilateral (DIS, current US\()**: The new disbursements (issuances) of multilateral loans.<br> **PPG, Multilateral (INT, current US\)): The amount of interest paid on multilateral loans.
PPG, Official Creditors (AMT, current US\()**: The total outstanding debt owed to official creditors.<br> **PPG, Official Creditors (DIS, current US\)): The new disbursements of loans from official creditors.

%%sql 
SELECT ROUND(SUM(debt)/1000000, 2) AS total_debt 
FROM inter_debt

Running query in 'inter_debttable'

total_debt
3079734.49

the total debt is about 30,797,934.49 million US dollars.

Which country owns the maximum amount of debt and what does that amount look like?

%%sql 
SELECT country_name, SUM(debt) AS total_debt 
FROM inter_debt 
GROUP BY country_name 
ORDER BY SUM(debt) DESC 
LIMIT 1

Running query in 'inter_debttable'

country_name	total_debt
China	285793494734.2

China owns the maximum amount of debt, approximately 285.8 billion US dollars. This large debt maybe is the result of rapid economic expansion, aggressive lending, and government-led development policies.

What is the average amount of debt owed by countries across different debt indicators?

%%sql 
SELECT indicator_code AS debt_indicator, indicator_name, AVG(debt) AS average_debt 
FROM inter_debt 
GROUP BY debt_indicator, indicator_name 
ORDER BY AVG(debt) DESC 
LIMIT 10

Running query in 'inter_debttable'

debt_indicator	indicator_name	average_debt
DT.AMT.DLXF.CD	"Principal repayments on external debt, long-term (AMT, current US$)"	5904868401.499194
DT.AMT.DPNG.CD	"Principal repayments on external debt, private nonguaranteed (PNG) (AMT, current US$)"	5161194333.812658
DT.DIS.DLXF.CD	"Disbursements on external debt, long-term (DIS, current US$)"	2152041216.890244
DT.DIS.OFFT.CD	"PPG, official creditors (DIS, current US$)"	1958983452.859836
DT.AMT.PRVT.CD	"PPG, private creditors (AMT, current US$)"	1803694101.9632652
DT.INT.DLXF.CD	"Interest payments on external debt, long-term (INT, current US$)"	1644024067.6508067
DT.DIS.BLAT.CD	"PPG, bilateral (DIS, current US$)"	1223139290.39823
DT.INT.DPNG.CD	"Interest payments on external debt, private nonguaranteed (PNG) (INT, current US$)"	1220410844.421519
DT.AMT.OFFT.CD	"PPG, official creditors (AMT, current US$)"	1191187963.0830643
DT.AMT.PBND.CD	"PPG, bonds (AMT, current US$)"	1082623947.6536233

Truncated to displaylimit of 10.

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.AMT.DLXF.CD')

Running query in 'inter_debttable'

country_name	indicator_name
China	"Principal repayments on external debt, long-term (AMT, current US$)"

Interpretation:

• China has the highest principal repayments on long-term external debt in the dataset. This suggests that China is actively repaying its long-term loans, either as part of scheduled payments or early repayments to reduce liabilities.

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.AMT.DPNG.CD')

Running query in 'inter_debttable'

country_name	indicator_name
China	"Principal repayments on external debt, private nonguaranteed (PNG) (AMT, current US$)"

Interpretation:

• China has the highest principal repayments on private, non-guaranteed external debt. This means that Chinese private companies, rather than the government, are making the largest repayments on external loans.

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.DIS.DLXF.CD')

Running query in 'inter_debttable'

country_name	indicator_name
Least developed countries: UN classification	"Disbursements on external debt, long-term (DIS, current US$)"

Interpretation:

• The entity that received the highest long-term external debt disbursement is the group of Least Developed Countries (LDCs) as classified by the UN. This means that collectively, these countries received the largest amount of new long-term external debt.

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.DIS.OFFT.CD')

Running query in 'inter_debttable'

country_name	indicator_name
Least developed countries: UN classification	"PPG, official creditors (DIS, current US$)"

Interpretation: - LDCs receiving the largest official creditor disbursements highlights global efforts to support underdeveloped economies. However, the long-term impact depends on whether these debts lead to sustainable growth or create a debt trap.

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.AMT.PRVT.CD')

Running query in 'inter_debttable'

country_name	indicator_name
Brazil	"PPG, private creditors (AMT, current US$)"

Interpretation: - Brazil has the highest amount of public and publicly guaranteed (PPG) debt owed to private creditors. This means Brazil has borrowed the most from private lenders, such as international banks and investors. Unlike official creditors (e.g., IMF, World Bank), private creditors lend at market rates, which can be riskier and more expensive.

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.INT.DLXF.CD')

Running query in 'inter_debttable'

country_name	indicator_name
Mexico	"Interest payments on external debt, long-term (INT, current US$)"

Interpretation: - Mexico pays the highest amount in interest on long-term external debt. This suggests Mexico has a large outstanding external debt balance with high interest costs.

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.DIS.BLAT.CD')

Running query in 'inter_debttable'

country_name	indicator_name
Least developed countries: UN classification	"PPG, bilateral (DIS, current US$)"

Interpretation: - The least developed countries (LDCs) receive the largest bilateral disbursements on external debt. This means these countries are heavily reliant on bilateral loans (loans from one country to another).

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.INT.DPNG.CD')                           

Running query in 'inter_debttable'

country_name	indicator_name
China	"Interest payments on external debt, private nonguaranteed (PNG) (INT, current US$)"

Interpretation: - China has the highest interest payments on private, nonguaranteed external debt. This means that private Chinese entities (companies, banks, institutions) owe a large amount of external debt that is not backed by the government. These loans could come from foreign investors, banks, or financial institutions.

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.AMT.OFFT.CD')

Running query in 'inter_debttable'

country_name	indicator_name
Least developed countries: UN classification	"PPG, official creditors (AMT, current US$)"

Interpretation: - The least developed countries (LDCs) as defined by the United Nations (UN) have the highest amount of public and publicly guaranteed (PPG) debt owed to official creditors. This means that low-income countries collectively have the largest total debt obligations to official lenders

%%sql 
SELECT inter_debt.country_name, inter_debt.indicator_name 
FROM inter_debt 
WHERE debt = (SELECT MAX(debt) FROM inter_debt WHERE indicator_code='DT.AMT.PBND.CD')

Running query in 'inter_debttable'

country_name	indicator_name
Mexico	"PPG, bonds (AMT, current US$)"

Interpretation:

• Mexico has the highest public and publicly guaranteed (PPG) debt through bond issuance. This means Mexico relies heavily on bonds as a method of borrowing, rather than loans from international organizations or bilateral agreements.

Overall Implications

1. Debt structures vary significantly by country, reflecting different economic strategies.
   • Developed or emerging economies (e.g., Brazil, Mexico, China) rely more on private creditors, bonds, and market-based financing.
   • Least developed countries depend on bilateral and official loans from international institutions.
2. Interest payments are a major concern for nations like Mexico and China.
   • Countries with high interest obligations must carefully manage their budgets to avoid financial instability.
3. Countries that rely on private creditors or bond markets face higher risks.
   • These debts are often subject to market volatility, interest rate hikes, and investor sentiment.